package Grade;
import java.sql.*;
public class Grade implements GradeDAO {

    String url = "jdbc:sqlserver://localhost:1433;databaseName=Student_Grade_Management_System;trustServerCertificate=true";
    String user = "ysy";
    String password = "051018";

    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }


    @Override
    public void insertGrade(String sid,int cid,double aGrade,double bGrade) {
        String query = "INSERT INTO 成绩单 VALUES (?,?,?,?)";

        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(query)) {

            ps.setString(1, sid);
            ps.setInt(2, cid);
            ps.setDouble(3, aGrade);
            ps.setDouble(4, bGrade);

            ps.executeUpdate();
            System.out.println("插入成功！");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    @Override
    public void updateGrade(String sid,int cid,double aGrade,double bGrade){

        String query = "UPDATE 成绩单 SET 平时成绩 = ?,考试成绩=? WHERE 学号 = ? AND 课程编号 = ?";
        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(query)) {
            ps.setDouble(1, aGrade);
            ps.setDouble(2, bGrade);
            ps.setString(3, sid);
            ps.setInt(4, cid);
            ps.executeUpdate();
            System.out.println("成绩更新成功！");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    @Override
    public void deleteGrade(String sid,int cid) {
        String query = "DELETE FROM 成绩单 WHERE 学号 = ? AND 课程编号 =?";

        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(query)) {

            ps.setString(1, sid);
            ps.setInt(2, cid);

            ps.executeUpdate();
            System.out.println("成绩删除成功！");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void getAllGrade(String Tname) {
        String sql = "select * from 总评成绩 where 教师姓名 = ?";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // 设置查询参数
            pstmt.setString(1, Tname);

            // 执行查询
            ResultSet rs = pstmt.executeQuery();

            // 获取 ResultSet 的元数据
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            // 打印列名
            for (int i = 1; i <= columnCount; i++) {
                System.out.print(metaData.getColumnName(i) + "\t");
            }
            System.out.println(); // 换行

            // 打印数据
            while (rs.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    String value = rs.getString(i);
                    System.out.print(value + "\t");
                }
                System.out.println(); // 每行数据输出后换行
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void getMyGrade(String id) {
        String sql = "select * from 总评成绩 where 学号 = ?";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // 设置查询参数
            pstmt.setString(1, id);

            // 执行查询
            ResultSet rs = pstmt.executeQuery();

            // 获取 ResultSet 的元数据
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            // 打印列名
            for (int i = 1; i <= columnCount; i++) {
                System.out.print(metaData.getColumnName(i) + "\t");
            }
            System.out.println(); // 换行

            // 打印数据
            while (rs.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    String value = rs.getString(i);
                    System.out.print(value + "\t");
                }
                System.out.println(); // 每行数据输出后换行
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


}
